﻿Imports System.Data.OleDb
Imports DTO

Public Class NhanVienDAO

    ' thêm 1 nhân viên
    Public Sub ThemNhanVien(ByVal nv As NhanVienDTO)
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "Insert into NhanVien (UserName, Pass, HoTen, SDT, DiaChi, idLoaiNguoiDung, GioiTinh, DaXoa) values (?, ?, ?, ?, ?, ?, ?, ?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@UserName", OleDbType.WChar)
        cmd.Parameters.Add("@Pass", OleDbType.WChar)
        cmd.Parameters.Add("@HoTen", OleDbType.WChar)
        cmd.Parameters.Add("@SDT", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@idLoaiNguoiDung", OleDbType.Integer)
        cmd.Parameters.Add("@GioiTinh", OleDbType.WChar)
        cmd.Parameters.Add("@DaXoa", OleDbType.Integer)

        cmd.Parameters("@UserName").Value = nv.laytenDN
        cmd.Parameters("@Pass").Value = nv.LayPass.Trim
        cmd.Parameters("@HoTen").Value = LCase(nv.LayTenHo.Trim)
        cmd.Parameters("@SDT").Value = nv.LaySDT
        cmd.Parameters("@DiaChi").Value = LCase(nv.LayDiaChi.Trim)
        cmd.Parameters("@idLoaiNguoiDung").Value = nv.LayIDNguoiDung
        cmd.Parameters("@GioiTinh").Value = nv.LayGioiTinh
        cmd.Parameters("@DaXoa").Value = 0

        cmd.ExecuteNonQuery()

        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function LayDanhSachNhanVien() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select UserName,Pass,HoTen,SDT,DiaChi,GioiTinh, idLoaiNguoiDung from NhanVien where DaXoa=0"
        '   sql = "select * from NguoiDung "
        'B4: Thuc thi chuoi strSQL
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function XoaNhanVien(ByVal user As NhanVienDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "update NhanVien set DaXoa=1 where UserName= ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@UserName", OleDbType.WChar)
        cmd.Parameters("@UserName").Value = user.laytenDN
        Dim i As Integer = 9
        i = cmd.ExecuteNonQuery()
        If i <> 9 Then
            kq = True
        Else
            kq = False
        End If

        cn.Close()
        Return kq
    End Function

    Public Function CapNhatNhanVien(ByVal nv As NhanVienDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update NhanVien set Pass= ?, HoTen = ?, SDT = ?, DiaChi = ?, idLoaiNguoiDung = ? where UserName= ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@Pass", OleDbType.WChar)
        cmd.Parameters.Add("@HoTen", OleDbType.WChar)
        cmd.Parameters.Add("@SDT", OleDbType.WChar)
        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        cmd.Parameters.Add("@idLoaiNguoiDung", OleDbType.Integer)
        cmd.Parameters.Add("@UserName", OleDbType.WChar)

        cmd.Parameters("@Pass").Value = nv.LayPass.Trim
        cmd.Parameters("@HoTen").Value = LCase(nv.LayTenHo.Trim)
        cmd.Parameters("@SDT").Value = nv.LaySDT
        cmd.Parameters("@DiaChi").Value = LCase(nv.LayDiaChi.Trim)
        cmd.Parameters("@idLoaiNguoiDung").Value = nv.LayIDNguoiDung

        cmd.Parameters("@UserName").Value = nv.laytenDN

        cmd.ExecuteNonQuery()
        Dim i As Integer = 0
        i = cmd.ExecuteNonQuery()
        If i <> 0 Then
            kq = True
        Else
            kq = False
        End If
        cn.Close()
        Return kq
    End Function

    Public Function LayLoai() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select TenLoaiNguoiDung from LoaiNguoiDung"
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Function TimTheoDiaChi(ByVal s As String) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from NhanVien where  DiaChi LIKE  ? + '%' " ' ' or DiaChi = ? "

        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@DiaChi", OleDbType.WChar)

        cmd.Parameters("@DiaChi").Value = LCase(s.Trim) ' ' ' tìm theo họ tên
        'cmd.Parameters("@DiaChi").Value = LCase(s.Trim)

        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function TimKiemNhanVien(ByVal s As String) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select UserName,Pass,HoTen,SDT,DiaChi,GioiTinh, idLoaiNguoiDung from NhanVien where UserName = ? or SDT=? or HoTen = ? " ' ' or DiaChi = ? "

        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@UserName", OleDbType.WChar)
        cmd.Parameters.Add("@SDT", OleDbType.WChar)
        cmd.Parameters.Add("@HoTen", OleDbType.WChar)
        'cmd.Parameters.Add("@DiaChi", OleDbType.WChar)

        cmd.Parameters("@UserName").Value = LCase(s.Trim) ' ' ' tìm theo tên đăng nhập 
        cmd.Parameters("@SDT").Value = LCase(s.Trim)  ' ' ' tìm theo số điện thoại
        cmd.Parameters("@HoTen").Value = LCase(s.Trim) ' ' ' tìm theo họ tên
        'cmd.Parameters("@DiaChi").Value = LCase(s.Trim)

        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

End Class
